using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CrudI.Interface;
using CrudI;

namespace SQLCodeCreator.DBInfo.SqlServer
{
    public class SqlServerConstraint : IConstraint
    {        
        private string _ColumnName;
        private string _ConstraintName;
        private ConstraintTypes _ConTypes;

        public string ColumnName
        {
            get { return _ColumnName; }
            set { _ColumnName = value; }
        }
        public string ConstraintName
        {
            get { return _ConstraintName; }
            set { _ConstraintName = value; }
        }
        public ConstraintTypes ConType
        {
            get { return _ConTypes; }
            set { _ConTypes = value; }
        }

        public SqlServerConstraint()
        {
            //List<SqlServerConstraint> fkPkConstraints = SelectFkPkConstraints(tableName);
        }

        internal static List<IConstraint> GetTableFkPkConstraints(string tableName)
        {
            return SelectFkPkConstraints(tableName);
        }

        private static List<IConstraint> SelectFkPkConstraints(string tableName)
        {
            //string sqlSelectDatabases = "select * from  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name = @TableName ";
            String sql = " SELECT c.COLUMN_NAME, c.DATA_TYPE, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE " +
                " FROM INFORMATION_SCHEMA.COLUMNS c " +
                " INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu " +
                " ON c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME " +
                " INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
                "    ON tc.TABLE_NAME = kcu.TABLE_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME " +
                " WHERE c.TABLE_NAME = @TableName AND (tc.CONSTRAINT_TYPE = 'PRIMARY KEY' OR tc.CONSTRAINT_TYPE = 'FOREIGN KEY' ) " + // also a UNIQUE
                " ORDER BY c.ORDINAL_POSITION ";

            DataTable dt = new DataTable("dtColumnsConstraints");
            SqlConnection conn = new SqlConnection(GlobaVars.sqlConnection.ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@TableName", tableName);
            try
            {
                cmd.Connection.Open();
                IDataReader rd = cmd.ExecuteReader();
                dt.Load(rd);
            }
            catch (Exception ex)
            {
                throw new DataException("Error opening constraints for DB select", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            List<IConstraint> conList = ConvertConstraintToBo(dt, tableName);
            return conList;
        }

        private static List<IConstraint> ConvertConstraintToBo(DataTable dt, string tableName)
        {
            List<IConstraint> conList = new List<IConstraint>();
            SqlServerConstraint cons;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cons = new SqlServerConstraint();
                if (dt.Columns.Contains("COLUMN_NAME"))
                {
                    cons.ColumnName = dt.Rows[i]["COLUMN_NAME"].ToString();
                }
                if (dt.Columns.Contains("CONSTRAINT_NAME"))
                {
                    cons.ConstraintName = dt.Rows[i]["CONSTRAINT_NAME"].ToString();
                }
                if (dt.Columns.Contains("CONSTRAINT_TYPE"))
                {
                    if (dt.Rows[i]["CONSTRAINT_TYPE"] != DBNull.Value)
                    {
                        if (dt.Rows[i]["CONSTRAINT_TYPE"].ToString() == "FOREIGN KEY")
                            cons.ConType = ConstraintTypes.FK;
                        else if (dt.Rows[i]["CONSTRAINT_TYPE"].ToString() == "PRIMARY KEY")
                            cons.ConType = ConstraintTypes.PK;
                    }
                }
                conList.Add(cons);
            }
            return conList;
        }
    }
}
